/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package aptech.eproject.logictics.dao.thinh;

import aptech.eproject.logictics.dao.kien.BookingManagementServiceRemote;
import aptech.eproject.logictics.dao.Util;
import aptech.eproject.logictics.db.*;
import aptech.eproject.logictics.dto.BranchReportDTO;
import aptech.eproject.logictics.dto.CustomerReportDTO;
import aptech.eproject.logictics.dto.DeliveryReportDTO;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.*;
import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.persistence.*;
import javax.sql.DataSource;

/**
 *
 * @author HOANGTHINH
 */
@Stateless(name = "stateless/ReportService")
public class ReportService implements ReportServiceRemote {

    @PersistenceContext(name = "LogicticsService")
    EntityManager em;
    @Resource(mappedName = "java:/Aptech/Eproject/DB/Logistics")
    private DataSource dataSource;

    private Query setDateParameter(Query q, Timestamp from, Timestamp to) {
        q.setParameter("date1", from);
        q.setParameter("date2", to);
        return q;
    }

    /**
     * Get Information to making Booking Statistics Report 
     * @param from - Date type
     * @param to - Date type
     * @return 
     *  HashMap type store value about report Informtaion
     */
    public HashMap BookingStatisticsReport(Date from, Date to) {
        HashMap hMapReport = new HashMap();
        Timestamp fromDate = new Timestamp(from.getTime());
        Timestamp toDate = new Timestamp(to.getTime());
        int pending = 0, accepted = 0, confirmed = 0, canceled = 0, rejected = 0, pickedUp = 0;
        try {
            // Total Delivery
            Query q = em.createQuery("SELECT count(d.deliveryId) FROM Delivery d WHERE d.bookedDate BETWEEN :date1 AND :date2");
            q = setDateParameter(q, fromDate, toDate);
            Long totalDelivery = (Long) q.getSingleResult();
            hMapReport.put("TotalDelivery", totalDelivery);

            //Get List Delivery Have Booked Date And Is Deleted='False'
            q = em.createQuery("SELECT d FROM Delivery d WHERE d.bookedDate BETWEEN :date1 AND :date2 ");
            q = setDateParameter(q, fromDate, toDate);
            List deliveries = q.getResultList();
            for (int i = 0; i < deliveries.size(); i++) {
                Delivery delivery = (Delivery) deliveries.get(i);
                switch (delivery.getBookingStatus()) {
                    case Accepted:
                        accepted++;
                        break;
                    case Canceled:
                        canceled++;
                        break;
                    case Confirmed:
                        confirmed++;
                        break;
                    case Rejected:
                        rejected++;
                        break;
                    case PickedUp:
                        pickedUp++;
                        break;
                    case Pending:
                        pending++;
                        break;
                    default:
                        break;
                }
            }
            hMapReport.put("Accept", accepted);
            hMapReport.put("Cancel", canceled);
            hMapReport.put("Confirm", confirmed);
            hMapReport.put("Reject", rejected);
            hMapReport.put("Pickup", pickedUp);
            hMapReport.put("Pending", pending);
            // Total Delivery Finished
            q = em.createQuery("SELECT count(d.deliveryId) FROM Delivery d WHERE d.isFinished='True' AND d.bookedDate BETWEEN :date1 AND :date2 ");
            q = setDateParameter(q, fromDate, toDate);
            q.getSingleResult();
            totalDelivery = (Long) q.getSingleResult();
            hMapReport.put("TotalDeliveryFinished", totalDelivery);
            //Total Delivery Deleted
            q = em.createQuery("SELECT count(d.deliveryId) FROM Delivery d WHERE d.isDeleted='True' AND d.bookedDate BETWEEN :date1 AND :date2 ");
            q = setDateParameter(q, fromDate, toDate);
            q.getSingleResult();
            totalDelivery = (Long) q.getSingleResult();
            hMapReport.put("TotalDeliveryDeleted", totalDelivery);
            //Punish Rate
            q = em.createQuery("SELECT SUM(d.pubnishRate) FROM Delivery d WHERE d.bookingStatus=?1 AND d.bookedDate BETWEEN :date1 AND :date2 ");
            q.setParameter(1, BookingStatus.Canceled);
            q = setDateParameter(q, fromDate, toDate);
            DecimalFormat decF = new DecimalFormat("#.##");
            BigDecimal pubnishRate = (BigDecimal) q.getSingleResult();
            if (pubnishRate != null) {
                hMapReport.put("PubnishRate", decF.format(pubnishRate));
            } else {
                hMapReport.put("PubnishRate", 0);
            }
            return hMapReport;
        } catch (NoResultException e) {
            System.err.println("Cannot find information  : " + e.getMessage());
            return null;
        } catch (NonUniqueResultException e) {
            System.err.println("There is more than one result  : " + e.getMessage());
            return null;
        } catch (Exception e) {
            System.err.println("Unknown error!!!!");
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Get a list of top 5 customers which have the most total rate
     * @param fromDate string in format "yyyy-MM-dd"
     * @param toDate string in format "yyyy-MM-dd"
     * @return
     *    List of {@link CustomerReportDTO} instances
     */
    public List<CustomerReportDTO> getCustomerReport(String fromDate, String toDate) {

        List<CustomerReportDTO> DTOs = new ArrayList<CustomerReportDTO>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String query =
                    "SELECT TOP 5 c.FULL_NAME, c.ADDRESS, ci.CITY_NAME, SUM(d.RATE) AS TOTALRATE "
                    + "FROM (CUSTOMER c JOIN CITY ci ON c.CITY_ID = ci.CITY_ID) "
                    + "     JOIN DELIVERY d ON c.[USER_NAME] = d.CONSIGNER_ID "
                    + "WHERE d.BOOKING_STATUS = 'PickedUp' "
                    + "   AND (d.CONFIRMED_DATE BETWEEN ? AND ?) "
                    + "GROUP BY c.FULL_NAME, c.ADDRESS, ci.CITY_NAME "
                    + "ORDER BY TOTALRATE DESC";
            PreparedStatement s = conn.prepareStatement(query);
            s.setString(1, fromDate);
            s.setString(2, toDate);

            ResultSet rs = s.executeQuery();
            while (rs.next()) {
                CustomerReportDTO dto = new CustomerReportDTO();
                dto.setCustomerName(rs.getString("FULL_NAME"));
                dto.setAddress(rs.getString("ADDRESS"));
                dto.setCityName(rs.getString("CITY_NAME"));
                dto.setTotalRate(rs.getDouble("TOTALRATE"));
                DTOs.add(dto);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return DTOs;
    }

    /**
     * Get a list of top 10 deliveries which have the most total rate
     * @param fromDate string in format "yyyy-MM-dd"
     * @param toDate string in format "yyyy-MM-dd"
     * @return
     *    List of {@link DeliveryReportDTO} instances
     */
    public List<DeliveryReportDTO> getDeliveryReport(String fromDate, String toDate) {

        List<DeliveryReportDTO> DTOs = new ArrayList<DeliveryReportDTO>();
        BookingManagementServiceRemote service = Util.lookupBookingManagementService();

        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String query =
                    "SELECT TOP 10 d.DELIVERY_ID, d.BOOKED_DATE, c.FULL_NAME, "
                    + "   d.START_BRANCH_ID, d.DESTINATION_BRANCH_ID, d.TOTAL_WEIGHT, d.RATE "
                    + "FROM DELIVERY d JOIN CUSTOMER c ON d.CONSIGNER_ID = c.[USER_NAME] "
                    + "WHERE d.BOOKING_STATUS = 'PickedUp' "
                    + "   AND (d.CONFIRMED_DATE BETWEEN ? AND ?) "
                    + "ORDER BY RATE DESC";
            PreparedStatement s = conn.prepareStatement(query);
            s.setString(1, fromDate);
            s.setString(2, toDate);

            ResultSet rs = s.executeQuery();
            while (rs.next()) {
                DeliveryReportDTO dto = new DeliveryReportDTO();
                dto.setBookedDate(rs.getTimestamp("BOOKED_DATE"));
                dto.setCustomerFullName(rs.getString("FULL_NAME"));
                dto.setTotalWeight(rs.getDouble("TOTAL_WEIGHT"));
                dto.setRate(rs.getBigDecimal("RATE"));
                dto.setDistance(service.findShortestDistance(
                        rs.getInt("START_BRANCH_ID"), rs.getInt("DESTINATION_BRANCH_ID")));
                DTOs.add(dto);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return DTOs;
    }

    /**
     * Get a list of top 5 branches which have the most revenue
     * @param fromDate string in format "yyyy-MM-dd"
     * @param toDate string in format "yyyy-MM-dd"
     * @return
     *    List of {@link BranchReportDTO} instances
     */
    public List<BranchReportDTO> getBranchReport(String fromDate, String toDate) {

        List<BranchReportDTO> DTOs = new ArrayList<BranchReportDTO>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String query =
                    "SELECT TOP 5 b.BRANCH_NAME, c.CITY_NAME, SUM(d.RATE) AS REVENUE, "
                    + "   SUM(d.COMISSION) AS COMMISSION "
                    + "FROM (BRANCH b JOIN CITY c ON b.CITY_ID = c.CITY_ID) "
                    + "   JOIN DELIVERY d ON b.BRANCH_ID = d.START_BRANCH_ID "
                    + "WHERE d.BOOKING_STATUS = 'PickedUp' "
                    + "   AND (d.CONFIRMED_DATE BETWEEN ? AND ?) "
                    + "GROUP BY b.BRANCH_NAME, c.CITY_NAME "
                    + "ORDER BY REVENUE DESC";
            PreparedStatement s = conn.prepareStatement(query);
            s.setString(1, fromDate);
            s.setString(2, toDate);

            ResultSet rs = s.executeQuery();
            while (rs.next()) {
                BranchReportDTO dto = new BranchReportDTO();
                dto.setBranchName(rs.getString("BRANCH_NAME"));
                dto.setCity(rs.getString("CITY_NAME"));
                dto.setRevenue(rs.getDouble("REVENUE"));
                dto.setCommission(rs.getDouble("COMMISSION"));
                DTOs.add(dto);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return DTOs;
    }
}
